from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

# 创建新工作簿
def cerate_excel_file():
    wb = Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    
    # 设置表头
    ws.append(["姓名", "年龄", "性别", "爱好"])

    
    # 定义下拉选项
    options = ["龙三", "张三", "李四", "王五", "赵六"]
    
    # 创建数据验证规则
    dv = DataValidation(type="list", formula1='"' + ",".join(options) + '"', allow_blank=True)
    
    # 将数据验证规则应用到A1单元格
    ws.add_data_validation(dv)
    dv.add(ws['A1'])
    
    # 可选：在其他单元格添加示例数据
    ws['B1'] = "姓名"
    ws['A2'] = "=A1"  # 引用A1单元格的值
    
    # 保存文件
    
    wb.save("dropdown_example.xlsx")
    print("Excel文件已创建，A1单元格包含下拉选择器")
def map_to_variables(filename, cell_reference='A1'):
    """
    将Excel中的选择映射到不同的变量或配置
    """
    # 定义完整的映射配置
    variable_mapping = {
        "龙三": {
            "number": 1,
            "code": "LS",
            "department": "技术部",
            "salary_level": "A"
        },
        "张三": {
            "number": 2,
            "code": "ZS",
            "department": "销售部",
            "salary_level": "B"
        },
        "李四": {
            "number": 3,
            "code": "LS",
            "department": "财务部",
            "salary_level": "C"
        },
        "王五": {
            "number": 4,
            "code": "WW",
            "department": "人事部",
            "salary_level": "B"
        },
        "赵六": {
            "number": 5,
            "code": "ZL",
            "department": "市场部",
            "salary_level": "A"
        }
    }
    
    try:
        wb = load_workbook(filename)
        ws = wb.active
        selected_name = ws[cell_reference].value
        
        if selected_name in variable_mapping:
            variables = variable_mapping[selected_name]
            print(f"映射结果: '{selected_name}' → {variables}")
            return variables
        else:
            print(f"错误: '{selected_name}' 不在映射表中")
            return None
    
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return None


# 使用示例
variables = map_to_variables("dropdown_example.xlsx")
if variables:
    print(f"编号: {variables['number']}")
    print(f"代码: {variables['code']}")
    print(f"部门: {variables['department']}")
    print(f"薪资等级: {variables['salary_level']}")